-- Coping with Legacy LONG Types, second example

set echo on

create or replace package long_help
authid current_user
as
function substr_of
      ( p_query in varchar2,
        p_from  in number,
        p_for   in number,
        p_name1 in varchar2 default NULL,
        p_bind1 in varchar2 default NULL,
        p_name2 in varchar2 default NULL,
        p_bind2 in varchar2 default NULL,
        p_name3 in varchar2 default NULL,
        p_bind3 in varchar2 default NULL,
        p_name4 in varchar2 default NULL,
        p_bind4 in varchar2 default NULL )
      return varchar2;
end;
/

create or replace package body long_help
  as
     g_cursor number := dbms_sql.open_cursor;
     g_query  varchar2(32765);

  procedure bind_variable( p_name in varchar2, p_value in varchar2 )
  is
  begin
      if ( p_name is not null )
      then
          dbms_sql.bind_variable( g_cursor, p_name, p_value );
      end if;
 end;

function substr_of
  ( p_query in varchar2,
    p_from  in number,
    p_for   in number,
    p_name1 in varchar2 default NULL,
    p_bind1 in varchar2 default NULL,
    p_name2 in varchar2 default NULL,
    p_bind2 in varchar2 default NULL,
    p_name3 in varchar2 default NULL,
    p_bind3 in varchar2 default NULL,
    p_name4 in varchar2 default NULL,
    p_bind4 in varchar2 default NULL )
  return varchar2
  as
      l_buffer       varchar2(4000);
      l_buffer_len   number;
begin
      if ( nvl(p_from,0) <= 0 )
      then
          raise_application_error
          (-20002, 'From must be >= 1 (positive numbers)' );
      end if;
      if ( nvl(p_for,0) not between 1 and 4000 )
      then
          raise_application_error
          (-20003, 'For must be between 1 and 4000' );
      end if;

      if ( p_query <> g_query or g_query is NULL )
      then
          if ( upper(trim(nvl(p_query,'x'))) not like 'SELECT%')
          then
              raise_application_error
              (-20001, 'This must be a select only' );
          end if;
          dbms_sql.parse( g_cursor, p_query, dbms_sql.native );
          g_query := p_query;
      end if;

      bind_variable( p_name1, p_bind1 );
      bind_variable( p_name2, p_bind2 );
      bind_variable( p_name3, p_bind3 );
      bind_variable( p_name4, p_bind4 );
      dbms_sql.define_column_long(g_cursor, 1);
      if (dbms_sql.execute_and_fetch(g_cursor)>0)
      then
          dbms_sql.column_value_long
          (g_cursor, 1, p_for, p_from-1,
           l_buffer, l_buffer_len );
      end if;
      return l_buffer;
  end substr_of;
end;
/ 

select *
from (
select table_owner, table_name, partition_name,
         long_help.substr_of
         ( 'select high_value
              from all_tab_partitions
             where table_owner = :o
               and table_name = :n
               and partition_name = :p',
            1, 4000,
            'o', table_owner,
            'n', table_name,
            'p', partition_name ) high_value
    from all_tab_partitions
     where table_owner = user
         )
   where high_value like '%2014%'
/
